package code.excel.poi;

import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;

import jxl.read.biff.BiffException;

/**
 * <p></p>
 *
 * @version 1.0
 * @author:huangwf
 * @date:2017/8/17 16:07
 * @history
 */

public class ExcelHandlePoiExample {


    /**
     * <p>读取Excel</p>
     *
     * @author:huangwf
     * @date:2017/8/17 15:37
     * @version 1.0
     * @history
     */
    public static void ReadExcelPoi(String filePath, String fileName) throws IOException, BiffException, InvalidFormatException {

        File xlsFile = new File(filePath + fileName);
        InputStream is = new FileInputStream(xlsFile);
        // 获得工作簿
        Workbook workbook = WorkbookFactory.create(is); //从poi-3.7.jar开始, WorkbookFactory类已经放进poi-ooxml-XXX.jar中了.
        // 获得工作表个数
        int sheetCount = workbook.getNumberOfSheets();
        // 遍历工作表
        for (int i = 0; i < sheetCount; i++) {
            Sheet sheet = workbook.getSheetAt(i);
            // 获得行数
            int rows = sheet.getLastRowNum() + 1;
            // 获得列数，先获得一行，在得到改行列数
            Row tmp = sheet.getRow(0);
            if (tmp == null) {
                continue;
            }
            int cols = tmp.getPhysicalNumberOfCells();
            // 读取数据
            for (int row = 0; row < rows; row++) {
                Row r = sheet.getRow(row);
                for (int col = 0; col < cols; col++) {
                    System.out.printf("%10s", r.getCell(col).getStringCellValue());
                }
                System.out.println();
            }
        }

    }

    /**
     * <p>写入Excel</p>
     *
     * @author:huangwf
     * @date:2017/8/17 15:37
     * @version 1.0
     * @history
     */
    public static void wirteExcelPoi(String filePath, String fileName) throws IOException {
        // 创建工作薄
        HSSFWorkbook workbook = new HSSFWorkbook();
        // 创建工作表
        HSSFSheet sheet = workbook.createSheet("poi");
        HSSFRow newRow;
        int rows = 10, cols = 10;
        for (int row = 0; row < rows; row++) {
            newRow = sheet.createRow(row);
            for (int col = 0; col < cols; col++) {
                // 向工作表中添加数据
                newRow.createCell(col).setCellValue("data" + row + col);
            }
        }
        newRow = sheet.createRow(rows);
        for (int col = 0; col < cols; col++) {
            newRow.createCell(col).setCellValue("data" + rows + col);
        }

        File xlsFile = new File(filePath + fileName);
        FileOutputStream xlsStream = new FileOutputStream(xlsFile,true);
        workbook.write(xlsStream);
    }

    public static void main(String[] args) {

        try {
            String path = "excel/out/excel/";
            String name = "poi.xls";
            wirteExcelPoi(path, name);
            ReadExcelPoi(path, name);
        } catch (IOException e) {
            e.printStackTrace();
        } catch (BiffException e) {
            e.printStackTrace();
        } catch (InvalidFormatException e) {
            e.printStackTrace();
        }
    }
}
